﻿using Hxj.Data;
using RecruitStudents.Utility.Helper;
using RecruitStudents.Utility.Model;
using System;
using System.Collections.Generic;
using System.Data;
using System.Linq;
using System.Text;
using System.Threading.Tasks;

namespace RecruitStudents.Client.DAL
{
    public class DALSecretary : Base.BaseService
    {
        public string SaveSecExaming(string strNum, string strGroupNo, string strExamRoomID, string strExamRoomName, System.Data.DataTable dtTeacher, string strYear)
        {
            string strStudentScoreWhere = string.Empty;
            if (dtTeacher != null)
            {
                foreach (DataRow dr in dtTeacher.Rows)
                {
                    string xh = dr["XH"].ToString();
                    if (strStudentScoreWhere.Length == 0)
                    {
                        strStudentScoreWhere = " and ISNULL(Teacher" + xh + ",'')!=''";
                    }
                    else
                    {
                        strStudentScoreWhere += " and ISNULL(Teacher" + xh + ",'')!=''";
                    }
                }
            }
            DbTrans trans = DbHelper.GetDatabase().BeginTransaction();
            try
            {
                if (strNum == "0")
                {
                    strNum = int.MaxValue.ToString();
                }
                string strSql = @"
                      IF not exists (select * from TB_Pad_Examing  where  ExamRoomNO='{0}' and  SignedID not in (select SignedID from TB_Pad_StudentScoreList where GroupNo='{1}' and ExamRoomNO='{0}' {3}))
                        begin
                            delete from TB_Pad_Examing  where GroupNo='{1}' and SignedID in (select SignedID from TB_Pad_StudentScoreList where GroupNo='{1}' and  ExamRoomNO='{0}')
                            declare @AGT_ID varchar(50)
                            set @AGT_ID = NEWID()
	                        INSERT INTO TB_Pad_Examing
	                        SELECT top {2} NEWID(),'{4}',GETDATE(),'{1}',@AGT_ID,a.SignedID ,a.ExamRoomNO,a.LotNo,a.Name,a.AllowExamID ,a.NMT_SignUpID ,a.IdentityCard ,a.ExamProvince ,a.Remark
	                        FROM TB_Pad_Queue as a left join TB_Exam_Signed as b on a.SignedID=b.SignedID where a.ExamRoomNO='{0}' and b.GroupNO='{1}' order by a.LotNo asc   
	                       
                            Update TB_Exam_SignUpStudentsSpeciality set ExamState=1 where AllowExamID in
                            (
                                select AllowExamID from TB_Pad_Examing
                            )
                            INSERT INTO TB_Pad_StudentScoreList
                               SELECT 
	                                top 0 
	                                NEWID(),
	                                '{4}',
	                                a.ExamRoomNO,
	                                c.ExamPlaceNO,
	                                c.SpecialityKindsNO,
	                                c.ExamLevel,
	                                GETDATE(),
	                                a.SignedID,
	                                a.LotNo,
	                                a.Name ,
	                                a.AllowExamID ,
	                                a.NMT_SignUpID ,
	                                a.IdentityCard ,
	                                a.ExamProvince ,
	                                b.GroupNO,
	                                @AGT_ID,
	                                0 AS IsUp,
	                                0 AS IsPass,
	                                0 AS IsPrint,
                                    0 AS IsPublish,
	                                NULL,NULL,0,
	                                NULL,NULL,0,
	                                NULL,NULL,0,
	                                NULL,NULL,0,
	                                NULL,NULL,0,
	                                NULL,NULL,0,
	                                NULL,NULL,0,
	                                NULL AS TatolScore,
	                                a.Remark
                                FROM      TB_Pad_Queue  as a 
                                left join TB_Exam_Signed as b on a.SignedID=b.SignedID 
                                left join TB_Exam_Entretenimientoh as c on b.EntretenimientohID=c.EntretenimientohID
                                where a.ExamRoomNO='{0}' and b.GroupNO='{1}'  order by a.LotNo asc                                 
                            delete from TB_Pad_Queue where SignedID in (select a.SignedID from TB_Pad_Examing as a,TB_Exam_Signed as b where a.SignedID=b.SignedID and a.ExamRoomNO='{0}' and b.GroupNO='{1}')                                               
                        end 
                    ";
                strSql = string.Format(strSql, strExamRoomID, strGroupNo, strNum, strStudentScoreWhere, strYear);
                DbHelper.GetDatabase().FromSql(strSql).SetDbTransaction(trans).ExecuteNonQuery();
                trans.Commit();
                return string.Empty;
            }
            catch (Exception ex)
            {
                trans.Rollback();
                return ex.Message;
            }
            finally
            {
                trans.Close();
            }
        }



        public List<TB_Pad_StudentScoreList> GetStudentScoreList(string strGroupNo, string strExamRoomID)
        {
            return DbHelper.GetDatabase().From<TB_Pad_StudentScoreList>()
                    .LeftJoin<TB_Pad_Examing>(
                    TB_Pad_StudentScoreList._.AGT_ID == TB_Pad_Examing._.AGT_ID
                    && TB_Pad_Examing._.GroupNo == TB_Pad_StudentScoreList._.GroupNo
                    && TB_Pad_Examing._.AllowExamID == TB_Pad_StudentScoreList._.AllowExamID
                    && TB_Pad_Examing._.ExamRoomNO == strExamRoomID
                    && TB_Pad_Examing._.GroupNo == strGroupNo)
                    .Where(TB_Pad_StudentScoreList._.AGT_ID == strGroupNo && TB_Pad_StudentScoreList._.ExamRoomNO == strExamRoomID)
                    .ToList();

        }
        public DataTable GetCalculateScoreTypeTable(string strSpecialityKindsNO, string strExamLevel)
        {
            string strSql = @"select a.SpecialityKindsNO,a.ExamLevel,b.CalculateScoreNo,b.CalculateScoreName,b.CalculateScoreType from TB_Sys_SpecialityLevelExamTypeMapping as a left join TB_Rule_CalculateScore as b on a.CalculateScoreID=b.CalculateScoreID
 left join TB_Rule_CalculateScoreDetail as c on b.CalculateScoreID=c.CalculateScoreID where a.SpecialityKindsNO='{0}' and ExamLevel='{1}'";
            strSql = string.Format(strSql, strSpecialityKindsNO, strExamLevel);

            return DbHelper.GetDatabase().FromSql(strSql).ToDataTable();
        }
    }
}
